<?php
/*
 * @Author: CHINAKAIYUAN<sunkaiyuan@namenode.cn>
 * @Date: 2022-02-04 16:53:56
 * @LastEditTime: 2023-09-24 16:40:11
 * @LastEditors: 孙开源 && sunkaiyuan@namenode.cn
 * @Description: 加料单
 */

namespace App\Http\Services\Finance;


use App\Http\Services\Service;
use App\Repositories\CompanyRepository;
use App\Repositories\FinanceBalanceRepository;
use App\Repositories\SubjectRepository;
use App\Repositories\FinanceVoucherDetailsRepository;
use \Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/**
 * @property FinanceBalanceRepository $repository
 * @property SubjectRepository $subject
 * @property FinanceVoucherDetailsRepository $voucher
 * @property CompanyRepository $company
 */
class BalanceService extends Service
    {

    public function boot(
        FinanceBalanceRepository $repository,
        SubjectRepository $subject,
        FinanceVoucherDetailsRepository $voucher,
        CompanyRepository $company
    ) {
        $this->repository = $repository;
        $this->subject    = $subject;
        $this->voucher    = $voucher;
        $this->company    = $company;
        }

    public function browser()
        {
        // SELECT value - LAG(value) OVER (ORDER BY id) AS difference
        // FROM your_table
        $request   = request();
        $begindate = "";
        $enddate   = "";
        $condition = $this->condition($begindate, $enddate);
        $derect    = 1;
        //期初  
        $prior_data_in                       = $this->voucher
            ->where("billdate", "=", '19700101')
            ->condition($this->conditionPrior($condition))
            ->addSelect(DB::raw("ifnull(sum(amount),0)  as amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_amount),0)  as debit_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_amount),0)  as credit_amount"))

            ->addSelect(DB::raw("ifnull(sum(invoice_amount),0)  as invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_invoice_amount),0)  as debit_invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_invoice_amount),0)  as credit_invoice_amount"))
            ->first();
        $prior_data_in->invoice_amount      *= $derect;
        $prior_data                          = $this->voucher
            ->where("billdate", "<", $begindate)
            ->where("billdate", "<>", '19700101')
            ->condition($this->conditionPrior($condition))
            ->addSelect(DB::raw("ifnull(sum(amount),0)  as amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_amount),0)  as debit_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_amount),0)  as credit_amount"))

            ->addSelect(DB::raw("ifnull(sum(invoice_amount),0)  as invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_invoice_amount),0)  as debit_invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_invoice_amount),0)  as credit_invoice_amount"))
            ->first();
        $prior_data->amount                 += $prior_data_in->amount;
        $prior_data->debit_amount           += $prior_data_in->debit_amount;
        $prior_data->credit_amount          += $prior_data_in->credit_amount;
        $prior_data->invoice_amount         += $prior_data_in->invoice_amount;
        $prior_data->debit_invoice_amount   += $prior_data_in->debit_invoice_amount;
        $prior_data->credit_invoice_amount  += $prior_data_in->credit_invoice_amount;
        //发生 
        $data = $this->voucher
            ->select()
            ->addSelect(DB::raw("sum(amount) over(order by billdate asc,id asc)+{$prior_data->amount}  as amount"))
            ->addSelect(DB::raw("sum(invoice_amount) over(order by billdate asc,id asc)+{$prior_data->invoice_amount}  as invoice_amount"))
            ->condition($condition)
            ->orderBy("billdate", "asc")
            ->orderBy("id", "asc")
            ->getGrid();
        //发生合计 
        $end_data = $this->voucher
            ->condition($condition)
            ->addSelect(DB::raw("ifnull(sum(amount),0)  as amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_amount),0)  as debit_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_amount),0)  as credit_amount"))

            ->addSelect(DB::raw("ifnull(sum(invoice_amount),0)  as invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_invoice_amount),0)  as debit_invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_invoice_amount),0)  as credit_invoice_amount"))
            ->first();

        if ($request->page == 1) {
            $data['rows']->prepend([
                'billdate'              => $begindate,
                'summary'               => '期初',
                'amount'                => round($prior_data->amount, 2),
                'debit_amount'          => 0,
                'credit_amount'         => 0,

                'invoice_amount'        => round($prior_data->invoice_amount, 2),
                'debit_invoice_amount'  => round($prior_data->debit_invoice_amount, 2),
                'credit_invoice_amount' => round($prior_data->credit_invoice_amount, 2),
            ]);
            }
        $data['footer'] = [
            [
                'summary'               => '当前合计',
                'debit_amount'          => round($end_data->debit_amount, 2),
                'credit_amount'         => round($end_data->credit_amount, 2),
                'amount'                => round($end_data->amount + $prior_data->amount, 2),
                'credit_invoice_amount' => round($end_data->credit_invoice_amount + $prior_data->credit_invoice_amount, 2),
                'debit_invoice_amount'  => round($end_data->debit_invoice_amount + $prior_data->debit_invoice_amount, 2),
                'invoice_amount'        => round($end_data->invoice_amount + $prior_data->invoice_amount, 2),
            ]
        ];
        return $data;
        }

    public function condition(&$begindate, &$enddate)
        {
        $request = request();
        if ($request->has("filter")) {
            $filters = $request->filter;
            foreach ($filters ?? [] as $keys => $filter) {
                foreach ($filter ?? [] as $key => $value)
                    if ($key == 'billdate') {
                        $begindate            = $value['value'][0] ?? $value[0]; //月初 
                        $enddate              = $value['value'][1] ?? $value[1]; //月末 
                        $filters[$keys][$key] = $value; //重置日期 
                        }
                }
            if (isEmpty($begindate)) {
                $value     = $request->billdate;
                $beinmonth = ($value[0] ?? date("Y-m"));
                $begindate = $beinmonth . "-01"; //月初

                $endmonth  = ($value[1] ?? date("Y-m"));
                $enddate   = date("Y-m-d", strtotime("{$endmonth}-1 +1 month -1 day")); //月末 
                $filters[] = ["billdate" => ['type' => 'bt', 'value' => [$begindate, $enddate]]];
                }
            $request->offsetSet("filter", $filters);
            }
        $request->offsetSet("sort", "id");
        $request->offsetSet("order", "asc");
        return $request;
        }
    public function conditionPrior($request)
        {
        $new_request = new Request();
        if ($request->has("filter")) {
            $filters = $request->filter;
            foreach ($filters ?? [] as $keys => $filter) {
                foreach ($filter ?? [] as $key => $value)
                    if ($key != 'billdate') {
                        $filterData[$keys][$key] = $filters[$keys][$key]; //重置日期 
                        }
                }
            }
        $new_request->offsetSet("filter", $filterData);
        return $new_request;
        }

    public function getSubjectAmount($subject, $begin, $end, $wherevalue, $wherefield = "subject_id", $otherwhere = null)
        {
        $merge_data = $subject->toArray();
        if ($otherwhere == null) {
            $otherwhere = function ($query)
                {
                };
            }

        $merge_data['prior_amount']  = 0;
        $merge_data['end_amount']    = 0;
        $merge_data['credit_amount'] = 0;
        $merge_data['debit_amount']  = 0;

        $merge_data['prior_invoice_amount']  = 0;
        $merge_data['debit_invoice_amount']  = 0;
        $merge_data['credit_invoice_amount'] = 0;

        //期初
        // DB::enableQueryLog(); 
        $prior_data = FinanceVoucherDetailsRepository::
            where("billdate", "<", $begin)
            ->where($wherefield, $wherevalue)
            ->where($otherwhere)
            ->select(
                DB::raw('ifnull(SUM(credit_amount),0) as prior_credit_amount'),
                DB::raw('ifnull(SUM(debit_amount),0) as prior_debit_amount'),
                DB::raw('ifnull(SUM(debit_invoice_amount),0) as prior_debit_invoice_amount'),
                DB::raw('ifnull(SUM(credit_invoice_amount),0) as prior_credit_invoice_amount'),
            )->first();
        // dd(DB::getQueryLog());
        //区间 
        $between_data = FinanceVoucherDetailsRepository::
            where("billdate", ">=", $begin)
            ->where("billdate", "<=", $end)
            ->where($wherefield, $wherevalue)
            ->where($otherwhere)
            ->select(
                DB::raw('ifnull(SUM(credit_amount),0) as credit_amount'),
                DB::raw('ifnull(SUM(debit_amount),0) as debit_amount'),
                DB::raw('ifnull(SUM(debit_invoice_amount),0) as debit_invoice_amount'),
                DB::raw('ifnull(SUM(credit_invoice_amount),0) as credit_invoice_amount'),
            )->first();

        //借-贷 期末 
        $computeData['prior_amount'] = ($prior_data->prior_debit_amount - $prior_data->prior_credit_amount) * ($subject->direct_id ?? $subject->finance_id);

        $computeData['end_amount'] = ($computeData['prior_amount'] + ($between_data->debit_amount - $between_data->credit_amount) * ($subject->direct_id ?? $subject->finance_id));

        $computeData['prior_invoice_amount'] = ($prior_data->prior_debit_invoice_amount - $prior_data->prior_credit_invoice_amount) * ($subject->direct_id ?? $subject->finance_id);

        $computeData['end_invoice_amount'] = ($computeData['prior_invoice_amount'] + ($between_data->debit_invoice_amount - $between_data->credit_invoice_amount) * ($subject->direct_id ?? $subject->finance_id));

        $computeData['prior_debit_amount']  = $prior_data->prior_debit_amount;
        $computeData['prior_credit_amount'] = $prior_data->prior_credit_amount;

        if ($subject->children?->count() > 0) {
            $computeData['prior_amount'] = $computeData['end_amount'] = $computeData['credit_amount'] = $computeData['debit_amount'] = 0;
            $merge_data['children'] = array_values($subject->children->map(function ($v) use ($begin, $end, $wherefield, &$computeData)
                {
                $children = $this->getSubjectAmount($v, $begin, $end, $v->id, $wherefield);

                $computeData['prior_amount']         += $children['prior_amount'];
                $computeData['prior_debit_amount']   += $children['prior_debit_amount'];
                $computeData['prior_credit_amount']  += $children['prior_credit_amount'];

                $computeData['end_amount']     += $children['end_amount'];
                $computeData['credit_amount']  += $children['credit_amount'];
                $computeData['debit_amount']  += $children['debit_amount'];

                return $children;
                })->filter(function ($v)
                    {
                    return (int) $v['prior_amount'] != 0 ||
                        (int) $v['end_amount'] != 0 ||
                        (int) $v['credit_amount'] != 0 ||
                        (int) $v['debit_amount'] != 0 ||
                        (int) $v['prior_invoice_amount'] != 0 ||
                        (int) $v['credit_invoice_amount'] != 0 ||
                        (int) $v['end_invoice_amount'] != 0 ||
                        (int) $v['debit_invoice_amount'] != 0;
                    })->toArray());
            }
        return array_merge($merge_data, $prior_data->toArray(), $between_data->toArray(), $computeData);

        }
    public function unsetDate()
        {
        $request = request();
        if ($request->has("filter")) {
            $filters = $request->filter;
            foreach ($filters ?? [] as $keys => $filter) {
                foreach ($filter ?? [] as $key => $value)
                    if ($key == 'date') {
                        unset($filters[$keys]);
                        }
                }
            $request->offsetSet("filter", $filters);
            }
        $request->offsetSet("sort", "code");
        $request->offsetSet("order", "asc");
        return $request;
        }

    public function companylist()
        {
        $request = request();

        $sort  = $request->sort ?? "id";
        $order = $request->order ?? "desc";
        $date  = $request->filter;
        if ($date) {
            $date = $date[0]['date'];
            }
        else {
            $date = date("Y-m-d");
            }

        $begin     = date("Y-m-d", strtotime($date[0]));
        $end       = date("Y-m-d", strtotime($date[1]));
        $prior_sql = FinanceVoucherDetailsRepository::where("billdate", "<", $begin)
            ->whereIn("subject_id", ['2202', '1122'])
            ->select(
                'company_id',
                DB::raw("SUM(credit_amount) as credit_amount"),
                DB::raw("SUM(debit_amount) as debit_amount"),
                DB::raw("SUM(debit_invoice_amount) as debit_invoice_amount"),
                DB::raw("SUM(credit_invoice_amount) as credit_invoice_amount"),
            )->groupBy('company_id')->getBuilder();

        $btw_sql = FinanceVoucherDetailsRepository::where("billdate", ">=", $begin)->where("billdate", "<=", $end)
            ->whereIn("subject_id", ['2202', '1122'])
            ->select(
                'company_id',
                DB::raw("SUM(credit_amount) as credit_amount"),
                DB::raw("SUM(debit_amount) as debit_amount"),
                DB::raw("SUM(debit_invoice_amount) as debit_invoice_amount"),
                DB::raw("SUM(credit_invoice_amount) as credit_invoice_amount"),
            )->groupBy('company_id')->getBuilder();

        $prior_amount  = "basedata_company.finance_id*(ifnull(prior.debit_amount,0)-ifnull(prior.credit_amount,0))";
        $debit_amount  = "ifnull(btw.debit_amount,0)";
        $credit_amount = "ifnull(btw.credit_amount,0)";

        $prior_amount_invoice  = "basedata_company.finance_id*(ifnull(prior.debit_invoice_amount,0)-ifnull(prior.credit_invoice_amount,0))";
        $debit_amount_invoice  = "ifnull(btw.debit_invoice_amount,0)";
        $credit_amount_invoice = "ifnull(btw.credit_invoice_amount,0)";
        $this->company
            ->condition($this->unsetDate())
            ->where(function ($query) use ($prior_amount, $debit_amount, $credit_amount, $prior_amount_invoice, $debit_amount_invoice, $credit_amount_invoice)
                {

                $query->orWhereRaw("{$prior_amount} <> 0");
                $query->orWhereRaw("{$debit_amount} <> 0");
                $query->orWhereRaw("{$credit_amount} <> 0");
                $query->orWhereRaw("{$prior_amount_invoice} <> 0");
                $query->orWhereRaw("{$debit_amount_invoice} <> 0");
                $query->orWhereRaw("{$credit_amount_invoice} <> 0");
                })
            ->select(
                'id',
                'name',
                'category_id',
                'code',
                DB::raw("{$prior_amount} as prior_amount"),
                DB::raw("{$debit_amount} as debit_amount"),
                DB::raw("{$credit_amount} as credit_amount"),
                DB::raw("{$prior_amount} + basedata_company.finance_id*({$debit_amount} - {$credit_amount}) as end_amount"),

                DB::raw("{$prior_amount_invoice} as prior_invoice_amount"),
                DB::raw("{$debit_amount_invoice} as debit_invoice_amount"),
                DB::raw("{$credit_amount_invoice} as credit_invoice_amount"),
                DB::raw("{$prior_amount_invoice} + basedata_company.finance_id*({$debit_amount_invoice} - {$credit_amount_invoice}) as end_invoice_amount"),
            )
            ->leftJoinSub($prior_sql, "prior", function ($join)
                {
                $join->on("prior.company_id", "basedata_company.id");
                })
            ->leftJoinSub($btw_sql, "btw", function ($join)
                {
                $join->on("btw.company_id", "basedata_company.id");
                })
            ->with(['category']);

        $data = $this->company->getGrid(function ($query) use ($sort, $order, $prior_amount, $debit_amount, $credit_amount, $prior_amount_invoice, $debit_amount_invoice, $credit_amount_invoice)
            {
            switch ($sort) {
                case 'prior_amount':
                    $query->orderByRaw("{$prior_amount} {$order}");
                    break;
                case 'debit_amount':
                    $query->orderByRaw("{$debit_amount} {$order}");
                    break;
                case 'credit_amount':
                    $query->orderByRaw("{$credit_amount} {$order}");
                    break;
                case 'end_amount':
                    $query->orderByRaw("{$prior_amount} + basedata_company.finance_id*({$debit_amount} - {$credit_amount}) {$order}");
                    break;
                case 'prior_invoice_amount':
                    $query->orderByRaw("{$prior_amount_invoice} {$order}");
                    break;
                case 'debit_invoice_amount':
                    $query->orderByRaw("{$debit_amount_invoice} {$order}");
                    break;
                case 'credit_invoice_amount':
                    $query->orderByRaw("{$credit_amount_invoice} {$order}");
                    break;
                case 'end_invoice_amount':
                    $query->orderByRaw("{$prior_amount_invoice} + basedata_company.finance_id*({$debit_amount_invoice} - {$credit_amount_invoice}) {$order}");
                    break;
                }
            }, null, null, false);
        return $data;
        }
    public function account()
        {
        return $this->list(1002);
        }
    public function list($parent_id = 0)
        {
        $begindate = "";
        $enddate   = "";
        $this->condition($begindate, $enddate);
        $rows = $this->subject->getMenuList($parent_id)->map(function ($v) use ($begindate, $enddate)
            {

            return $this->getSubjectAmount($v, $begindate, $enddate, $v->id);
            })->filter(function ($v)
                {

                return (int) $v['prior_amount'] != 0 ||
                    (int) $v['end_amount'] != 0 ||
                    (int) $v['credit_amount'] != 0 ||
                    (int) $v['debit_amount'] != 0 ||

                    (int) $v['prior_invoice_amount'] != 0 ||
                    (int) $v['credit_invoice_amount'] != 0 ||
                    (int) $v['end_invoice_amount'] != 0 ||
                    (int) $v['debit_invoice_amount'] != 0;
                })->toArray();

        return array_values($rows);
        }

    public function printReport()
        {
        $request   = request();
        $begindate = "";
        $enddate   = "";

        $request->query->remove('name');
        $condition = $this->condition($begindate, $enddate);
        $derect    = 1;
        //期初  
        $prior_data_in = $this->voucher
            ->where("billdate", "=", '19700101')
            ->condition($this->conditionPrior($condition))
            ->addSelect(DB::raw("ifnull(sum(amount),0)  as amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_amount),0)  as debit_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_amount),0)  as credit_amount"))

            ->addSelect(DB::raw("ifnull(sum(invoice_amount),0)  as invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_invoice_amount),0)  as debit_invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_invoice_amount),0)  as credit_invoice_amount"))
            ->first();

        $prior_data_in->invoice_amount      *= $derect;
        $prior_data                          = $this->voucher
            ->where("billdate", "<", $begindate)
            ->where("billdate", "<>", '19700101')
            ->condition($this->conditionPrior($condition))
            ->addSelect(DB::raw("ifnull(sum(amount),0)  as amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_amount),0)  as debit_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_amount),0)  as credit_amount"))

            ->addSelect(DB::raw("ifnull(sum(invoice_amount),0)  as invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_invoice_amount),0)  as debit_invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_invoice_amount),0)  as credit_invoice_amount"))
            ->first();
        $prior_data->amount                 += $prior_data_in->amount;
        $prior_data->debit_amount           += $prior_data_in->debit_amount;
        $prior_data->credit_amount          += $prior_data_in->credit_amount;
        $prior_data->invoice_amount         += $prior_data_in->invoice_amount;
        $prior_data->debit_invoice_amount   += $prior_data_in->debit_invoice_amount;
        $prior_data->credit_invoice_amount  += $prior_data_in->credit_invoice_amount;

        $end_data       = $this->voucher
            ->condition($condition)
            ->addSelect(DB::raw("ifnull(sum(amount),0)  as amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_amount),0)  as debit_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_amount),0)  as credit_amount"))

            ->addSelect(DB::raw("ifnull(sum(invoice_amount),0)  as invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(debit_invoice_amount),0)  as debit_invoice_amount"))
            ->addSelect(DB::raw("ifnull(sum(credit_invoice_amount),0)  as credit_invoice_amount"))
            ->first();
        $amount         = $end_data->amount + $prior_data->amount;
        $invoice_amount = $end_data->invoice_amount + $prior_data->invoice_amount;
        $data           = $this->voucher
            ->select()
            ->addSelect(DB::raw("case when invoice_amount>0 then '含' else '不含' end as tax_name"))
            ->addSelect(DB::raw("{$end_data->debit_amount} as end_debit_amount"))
            ->addSelect(DB::raw("{$end_data->credit_amount} as end_credit_amount"))
            ->addSelect(DB::raw("{$amount} as end_amount"))
            ->addSelect(DB::raw("{$end_data->credit_invoice_amount} as end_credit_invoice_amount"))
            ->addSelect(DB::raw("{$end_data->debit_invoice_amount} as end_debit_invoice_amount"))
            ->addSelect(DB::raw("{$invoice_amount} as end_invoice_amount"))
            ->addSelect(DB::raw("sum(amount) over(order by billdate asc,id asc)+{$prior_data->amount}  as amount"))
            ->addSelect(DB::raw("sum(invoice_amount) over(order by billdate asc,id asc)+{$prior_data->invoice_amount}  as invoice_amount"))
            ->condition($condition)
            ->orderBy("billdate", "asc")
            ->getList();

        $data->prepend([
            'billdate'              => $begindate,
            'main.source_value'     => '上期结欠',
            'company.name'          => $data->first()?->company->name,
            'amount'                => $prior_data->amount,
            'debit_amount'          => 0,
            'credit_amount'         => 0,
            'enddate'               => $enddate,
            'invoice_amount'        => $prior_data->invoice_amount,
            'debit_invoice_amount'  => 0,
            'credit_invoice_amount' => $prior_data->credit_invoice_amount,
        ]);



        return createReport($data->toArray());
        }


    }